Project 1: Supply Chain Sustainability#

Link to Article

Packages and Settings#

import pandas as pd
import warnings
import plotly.express as px
import numpy as np
pd.set_option('display.max_colwidth', 0)
pd.set_option('display.max_columns', None)
pd.options.display.max_seq_items = 2000
warnings.filterwarnings('ignore')
%%html
<style>
.dataframe td {
    white-space: nowrap;
}
</style>

Initial Datasets Import#

Import Shipped Order Lines#

df_lines = pd.read_csv('../data/order_lines.csv', index_col = 0)
print("{:,} order lines to process".format(len(df_lines)))
df_lines.head()
5,208 order lines to process
Date Month-Year Warehouse Code Customer Code Order Number Order Line Number Item Code Units Euros
112 2021-01-04 00:00:00.000 1-2021 3403434 5002915-15 20247100 1 963543-43 2.0 381.47
14485 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 1 8902753-53 250.0 187.03
14486 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 2 8308591-91 500.0 452.45
14487 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 3 8308621-21 500.0 452.45
14488 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 4 8022921-21 400.0 567.16

Import Master Data: Unit of Measure Conversions to (kg)#

df_uom = pd.read_csv('../data/uom_conversions.csv', index_col = 0)
print("{:,} Unit of Measure Conversions".format(len(df_uom)))

# Join
df_join = df_lines.copy()
COLS_JOIN = ['Item Code']
df_join = pd.merge(df_join, df_uom, on=COLS_JOIN, how='left', suffixes=('', '_y'))
df_join.drop(df_join.filter(regex='_y$').columns.tolist(),axis=1, inplace=True)
print("{:,} records".format(len(df_join)))
df_join.head()
557 Unit of Measure Conversions
5,208 records
Date Month-Year Warehouse Code Customer Code Order Number Order Line Number Item Code Units Euros Conversion Ratio
0 2021-01-04 00:00:00.000 1-2021 3403434 5002915-15 20247100 1 963543-43 2.0 381.47 56.58400
1 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 1 8902753-53 250.0 187.03 0.03200
2 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 2 8308591-91 500.0 452.45 0.02219
3 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 3 8308621-21 500.0 452.45 0.02219
4 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 4 8022921-21 400.0 567.16 0.03200

Import Distances#

df_dist = pd.read_csv('../data/' + 'distances.csv', index_col = 0)
# Location
df_dist['Location'] = df_dist['Customer Country'].astype(str) + ', ' + df_dist['Customer City'].astype(str)
df_dist.head()
Warehouse Code Warehouse Name Warehouse Country Warehouse City Customer Code Customer Country Customer City Road Rail Sea Air Location
19 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5002915-15 FRANCE LES ANGLES 765.728 0.0 0.0 0.0 FRANCE, LES ANGLES
610 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5397843-43 FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE
676 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340669-69 GERMANY PEINE - WOLTORF 856.000 0.0 0.0 0.0 GERMANY, PEINE - WOLTORF
682 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340672-72 FRANCE BELLEVILLE 52.694 0.0 0.0 0.0 FRANCE, BELLEVILLE
1375 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5029913-13 FRANCE MOISSY-CRAMAYEL 288.437 0.0 0.0 0.0 FRANCE, MOISSY-CRAMAYEL

Import Cities GPS Locations#

df_gps = pd.read_csv('../data/' + 'gps_locations.csv', index_col = 0)
print("{:,} Locations".format(len(df_gps)))
df_gps.head()
19 Locations
Location GPS 1 GPS 2
867 BULGARIA, DOLNI BOGROV 42.701462 23.490811
1087 FRANCE, AUBROMETZ 50.303749 2.176058
1153 FRANCE, BELLEVILLE 48.871184 2.386682
1371 FRANCE, CLICHY CEDEX 48.904099 2.304123
1534 FRANCE, GAEL 48.116682 -2.234640

Data Processing#

Merge Distance with GPS Locations#

df_dist = pd.merge(df_dist, df_gps, on='Location', how='left', suffixes=('', '_y'))
df_dist.drop(df_dist.filter(regex='_y$').columns.tolist(),axis=1, inplace=True)
df_dist
Warehouse Code Warehouse Name Warehouse Country Warehouse City Customer Code Customer Country Customer City Road Rail Sea Air Location GPS 1 GPS 2
0 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5002915-15 FRANCE LES ANGLES 765.728 0.0 0.0 0.0 FRANCE, LES ANGLES 42.579678 2.048950
1 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5397843-43 FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369
2 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340669-69 GERMANY PEINE - WOLTORF 856.000 0.0 0.0 0.0 GERMANY, PEINE - WOLTORF 52.304594 10.312521
3 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340672-72 FRANCE BELLEVILLE 52.694 0.0 0.0 0.0 FRANCE, BELLEVILLE 48.871184 2.386682
4 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5029913-13 FRANCE MOISSY-CRAMAYEL 288.437 0.0 0.0 0.0 FRANCE, MOISSY-CRAMAYEL 48.624666 2.595756
5 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5003451-51 FRANCE LEVALLOIS PERRET 295.165 0.0 0.0 0.0 FRANCE, LEVALLOIS PERRET 48.894640 2.287419
6 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5293132-32 FRANCE HERLIN LE SEC 353.590 0.0 0.0 0.0 FRANCE, HERLIN LE SEC 50.354429 2.331766
7 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5003269-69 FRANCE MACON 478.723 0.0 0.0 0.0 FRANCE, MACON 48.878552 2.280117
8 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5003474-74 FRANCE ORLY 287.110 0.0 0.0 0.0 FRANCE, ORLY 48.740227 2.402965
9 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5003476-76 FRANCE VITRY SUR SEINE 284.851 0.0 0.0 0.0 FRANCE, VITRY SUR SEINE 48.789263 2.395103
10 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 115-15 FRANCE CLICHY CEDEX 293.858 0.0 0.0 0.0 FRANCE, CLICHY CEDEX 48.904099 2.304123
11 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5006517-17 FRANCE SERRIS 256.250 0.0 0.0 0.0 FRANCE, SERRIS 48.845309 2.787302
12 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5335348-48 UNITED KINGDOM BRISTOL 626.000 0.0 44.0 0.0 UNITED KINGDOM, BRISTOL 51.468489 -2.590717
13 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 9001746-46 FRANCE AUBROMETZ 357.151 0.0 0.0 0.0 FRANCE, AUBROMETZ 50.303749 2.176058
14 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 9066250-50 FRANCE GAEL 667.613 0.0 0.0 0.0 FRANCE, GAEL 48.116682 -2.234640
15 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5334855-55 UNITED KINGDOM KENT 409.000 0.0 44.0 0.0 UNITED KINGDOM, KENT 51.196396 0.742644
16 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5494889-89 FRANCE GRETZ ARMAINVILLIERS 268.336 0.0 0.0 0.0 FRANCE, GRETZ ARMAINVILLIERS 48.753586 2.727228
17 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5262267-67 MAURITANIA NOUAKCHOTT 300.000 0.0 3739.0 0.0 MAURITANIA, NOUAKCHOTT 18.067171 -15.953561
18 3402002 WAREHOUSE PARIS AREA 2 FRANCE MONTMIRAIL 5486697-97 BULGARIA DOLNI BOGROV 2163.000 0.0 0.0 0.0 BULGARIA, DOLNI BOGROV 42.701462 23.490811

Final Join to build records#

COLS_JOIN = ['Warehouse Code', 'Customer Code']
df_join = pd.merge(df_join, df_dist, on = COLS_JOIN, how='left', suffixes=('', '_y'))
df_join.drop(df_join.filter(regex='_y$').columns.tolist(),axis=1, inplace=True)
print("{:,} records".format(len(df_join)))
df_join
5,208 records
Date Month-Year Warehouse Code Customer Code Order Number Order Line Number Item Code Units Euros Conversion Ratio Warehouse Name Warehouse Country Warehouse City Customer Country Customer City Road Rail Sea Air Location GPS 1 GPS 2
0 2021-01-04 00:00:00.000 1-2021 3403434 5002915-15 20247100 1 963543-43 2.0 381.47 56.58400 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE LES ANGLES 765.728 0.0 0.0 0.0 FRANCE, LES ANGLES 42.579678 2.048950
1 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 1 8902753-53 250.0 187.03 0.03200 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369
2 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 2 8308591-91 500.0 452.45 0.02219 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369
3 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 3 8308621-21 500.0 452.45 0.02219 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369
4 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 4 8022921-21 400.0 567.16 0.03200 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5203 2021-10-28 00:00:00.000 10-2021 3403434 5397843-43 21089497 84 802065-65 80.0 72.96 0.02200 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369
5204 2021-10-28 00:00:00.000 10-2021 3403434 5397843-43 21089497 85 942082-82 200.0 391.88 0.03200 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369
5205 2021-10-28 00:00:00.000 10-2021 3403434 5397843-43 21089497 86 8022922-22 400.0 581.40 0.03200 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369
5206 2021-11-01 00:00:00.000 10-2021 3403434 5340669-69 21004473 1 982884-84 2000.0 2980.00 0.14600 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE GERMANY PEINE - WOLTORF 856.000 0.0 0.0 0.0 GERMANY, PEINE - WOLTORF 52.304594 10.312521
5207 2021-11-01 00:00:00.000 10-2021 3403434 5340669-69 21004473 2 983347-47 2400.0 4848.00 0.16950 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE GERMANY PEINE - WOLTORF 856.000 0.0 0.0 0.0 GERMANY, PEINE - WOLTORF 52.304594 10.312521

5208 rows × 22 columns

Calculation at order line level#

# Calculation @ line level
df_line = df_join.copy()
df_line['KG'] = df_line['Units'] * df_line['Conversion Ratio']

dict_co2e = dict(zip(['Air' ,'Sea', 'Road', 'Rail'], [2.1, 0.01, 0.096, 0.028]))
MODES = ['Road', 'Rail','Sea', 'Air']
for mode in MODES:
    df_line['CO2 ' + mode] = df_line['KG'].astype(float)/1000 * df_line[mode].astype(float) * dict_co2e[mode]
df_line['CO2 Total'] = df_line[['CO2 ' + mode for mode in MODES]].sum(axis = 1)
df_line.to_csv('../data/detailed_report.csv')
df_line.head()
Date Month-Year Warehouse Code Customer Code Order Number Order Line Number Item Code Units Euros Conversion Ratio Warehouse Name Warehouse Country Warehouse City Customer Country Customer City Road Rail Sea Air Location GPS 1 GPS 2 KG CO2 Road CO2 Rail CO2 Sea CO2 Air CO2 Total
0 2021-01-04 00:00:00.000 1-2021 3403434 5002915-15 20247100 1 963543-43 2.0 381.47 56.58400 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE LES ANGLES 765.728 0.0 0.0 0.0 FRANCE, LES ANGLES 42.579678 2.048950 113.168 8.318967 0.0 0.0 0.0 8.318967
1 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 1 8902753-53 250.0 187.03 0.03200 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369 8.000 0.216183 0.0 0.0 0.0 0.216183
2 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 2 8308591-91 500.0 452.45 0.02219 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369 11.095 0.299818 0.0 0.0 0.0 0.299818
3 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 3 8308621-21 500.0 452.45 0.02219 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369 11.095 0.299818 0.0 0.0 0.0 0.299818
4 2021-01-07 00:00:00.000 1-2021 3403434 5397843-43 20258239 4 8022921-21 400.0 567.16 0.03200 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE FRANCE SUCY EN BRIE 281.488 0.0 0.0 0.0 FRANCE, SUCY EN BRIE 48.768810 2.537369 12.800 0.345892 0.0 0.0 0.0 0.345892

Calculation at order level#

# Calculate Weight (KG)
df_join['KG'] = df_join['Units'] * df_join['Conversion Ratio']

# Agg by order
GPBY_ORDER = ['Date', 'Month-Year', 
        'Warehouse Code', 'Warehouse Name', 'Warehouse Country', 'Warehouse City',
        'Customer Code', 'Customer Country', 'Customer City','Location', 'GPS 1', 'GPS 2', 
        'Road', 'Rail', 'Sea', 'Air',
        'Order Number']
df_agg = pd.DataFrame(df_join.groupby(GPBY_ORDER)[['Units', 'KG']].sum())
df_agg.reset_index(inplace = True)
df_agg.head()
Date Month-Year Warehouse Code Warehouse Name Warehouse Country Warehouse City Customer Code Customer Country Customer City Location GPS 1 GPS 2 Road Rail Sea Air Order Number Units KG
0 2021-01-04 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5002915-15 FRANCE LES ANGLES FRANCE, LES ANGLES 42.579678 2.048950 765.728 0.0 0.0 0.0 20247100 2.0 113.16800
1 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340669-69 GERMANY PEINE - WOLTORF GERMANY, PEINE - WOLTORF 52.304594 10.312521 856.000 0.0 0.0 0.0 20203388 153.0 2530.32471
2 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340672-72 FRANCE BELLEVILLE FRANCE, BELLEVILLE 48.871184 2.386682 52.694 0.0 0.0 0.0 20203383 128.0 2116.87296
3 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5397843-43 FRANCE SUCY EN BRIE FRANCE, SUCY EN BRIE 48.768810 2.537369 281.488 0.0 0.0 0.0 20258239 2771.0 71.45232
4 2021-01-08 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5003451-51 FRANCE LEVALLOIS PERRET FRANCE, LEVALLOIS PERRET 48.894640 2.287419 295.165 0.0 0.0 0.0 21000061 2125.0 111.95200

Calculate CO2 = f(KG, Ratios)#

# CO2 Emissions
dict_co2e = dict(zip(['Air' ,'Sea', 'Road', 'Rail'], [2.1, 0.01, 0.096, 0.028]))
MODES = ['Road', 'Rail','Sea', 'Air']
for mode in MODES:
    df_agg['CO2 ' + mode] = df_agg['KG'].astype(float)/1000 * df_agg[mode].astype(float) * dict_co2e[mode]
df_agg['CO2 Total'] = df_agg[['CO2 ' + mode for mode in MODES]].sum(axis = 1)
df_agg.head()
Date Month-Year Warehouse Code Warehouse Name Warehouse Country Warehouse City Customer Code Customer Country Customer City Location GPS 1 GPS 2 Road Rail Sea Air Order Number Units KG CO2 Road CO2 Rail CO2 Sea CO2 Air CO2 Total
0 2021-01-04 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5002915-15 FRANCE LES ANGLES FRANCE, LES ANGLES 42.579678 2.048950 765.728 0.0 0.0 0.0 20247100 2.0 113.16800 8.318967 0.0 0.0 0.0 8.318967
1 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340669-69 GERMANY PEINE - WOLTORF GERMANY, PEINE - WOLTORF 52.304594 10.312521 856.000 0.0 0.0 0.0 20203388 153.0 2530.32471 207.931963 0.0 0.0 0.0 207.931963
2 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340672-72 FRANCE BELLEVILLE FRANCE, BELLEVILLE 48.871184 2.386682 52.694 0.0 0.0 0.0 20203383 128.0 2116.87296 10.708464 0.0 0.0 0.0 10.708464
3 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5397843-43 FRANCE SUCY EN BRIE FRANCE, SUCY EN BRIE 48.768810 2.537369 281.488 0.0 0.0 0.0 20258239 2771.0 71.45232 1.930845 0.0 0.0 0.0 1.930845
4 2021-01-08 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5003451-51 FRANCE LEVALLOIS PERRET FRANCE, LEVALLOIS PERRET 48.894640 2.287419 295.165 0.0 0.0 0.0 21000061 2125.0 111.95200 3.172254 0.0 0.0 0.0 3.172254

Final mapping for visualization#

# Mapping the delivery Mode
df_agg['Delivery Mode'] = df_agg[MODES].astype(float).apply(
    lambda t: [mode if t[mode]>0 else '-' for mode in MODES], axis = 1)
dict_map = dict(zip(df_agg['Delivery Mode'].astype(str).unique(), 
  [i.replace(", '-'",'').replace("'-'",'').replace("'",'') for i in df_agg['Delivery Mode'].astype(str).unique()]))
df_agg['Delivery Mode'] = df_agg['Delivery Mode'].astype(str).map(dict_map)
df_agg
Date Month-Year Warehouse Code Warehouse Name Warehouse Country Warehouse City Customer Code Customer Country Customer City Location GPS 1 GPS 2 Road Rail Sea Air Order Number Units KG CO2 Road CO2 Rail CO2 Sea CO2 Air CO2 Total Delivery Mode
0 2021-01-04 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5002915-15 FRANCE LES ANGLES FRANCE, LES ANGLES 42.579678 2.048950 765.728 0.0 0.0 0.0 20247100 2.0 113.16800 8.318967 0.0 0.000000 0.0 8.318967 [Road]
1 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340669-69 GERMANY PEINE - WOLTORF GERMANY, PEINE - WOLTORF 52.304594 10.312521 856.000 0.0 0.0 0.0 20203388 153.0 2530.32471 207.931963 0.0 0.000000 0.0 207.931963 [Road]
2 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340672-72 FRANCE BELLEVILLE FRANCE, BELLEVILLE 48.871184 2.386682 52.694 0.0 0.0 0.0 20203383 128.0 2116.87296 10.708464 0.0 0.000000 0.0 10.708464 [Road]
3 2021-01-07 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5397843-43 FRANCE SUCY EN BRIE FRANCE, SUCY EN BRIE 48.768810 2.537369 281.488 0.0 0.0 0.0 20258239 2771.0 71.45232 1.930845 0.0 0.000000 0.0 1.930845 [Road]
4 2021-01-08 00:00:00.000 1-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5003451-51 FRANCE LEVALLOIS PERRET FRANCE, LEVALLOIS PERRET 48.894640 2.287419 295.165 0.0 0.0 0.0 21000061 2125.0 111.95200 3.172254 0.0 0.000000 0.0 3.172254 [Road]
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
286 2021-10-26 00:00:00.000 10-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340672-72 FRANCE BELLEVILLE FRANCE, BELLEVILLE 48.871184 2.386682 52.694 0.0 0.0 0.0 21004398 9040.0 416.74880 2.108175 0.0 0.000000 0.0 2.108175 [Road]
287 2021-10-27 00:00:00.000 10-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5029913-13 FRANCE MOISSY-CRAMAYEL FRANCE, MOISSY-CRAMAYEL 48.624666 2.595756 288.437 0.0 0.0 0.0 21089663 13515.0 493.52420 13.665661 0.0 0.000000 0.0 13.665661 [Road]
288 2021-10-27 00:00:00.000 10-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5334855-55 UNITED KINGDOM KENT UNITED KINGDOM, KENT 51.196396 0.742644 409.000 0.0 44.0 0.0 21009276 450.0 35.34930 1.387955 0.0 0.015554 0.0 1.403509 [Road, Sea]
289 2021-10-28 00:00:00.000 10-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5397843-43 FRANCE SUCY EN BRIE FRANCE, SUCY EN BRIE 48.768810 2.537369 281.488 0.0 0.0 0.0 21089497 11831.0 367.71380 9.936674 0.0 0.000000 0.0 9.936674 [Road]
290 2021-11-01 00:00:00.000 10-2021 3403434 WAREHOUSE PARIS AREA 1 FRANCE CHALONS-EN-CHAMPAGNE 5340669-69 GERMANY PEINE - WOLTORF GERMANY, PEINE - WOLTORF 52.304594 10.312521 856.000 0.0 0.0 0.0 21004473 4400.0 698.80000 57.424589 0.0 0.000000 0.0 57.424589 [Road]

291 rows × 25 columns

# Save Final Report
df_agg.to_csv('../data/final_report.csv')

Visualisation#

df = pd.read_csv("../data/final_report.csv")

fig = px.scatter_mapbox(
    df,
    lat='GPS 1',             # Latitude column
    lon='GPS 2',             # Longitude column
    color='Delivery Mode',    # Color bubbles by transport mode
    size='CO2 Total',         # Bubble size by total CO2
    hover_name='Location',    # What shows up when hovering over a point
    hover_data=['CO2 Total'], # Additional info in the hover tooltip
    zoom=4,                   # Map zoom level
    height=600                # Figure height in pixels
)

# Use an open-source map style so no token is needed:
fig.update_layout(mapbox_style="carto-positron")
fig.update_layout(title="CO₂ Emissions by Delivery Mode")

fig.show()
df = pd.read_csv("../data/detailed_report.csv")
df_agg = df.groupby(["Customer Country", "Item Code"])["CO2 Total"].sum().reset_index()
fig = px.bar(
    df_agg,
    x="CO2 Total",
    y="Customer Country",
    color="Item Code",
    orientation="h",    # Horizontal bars
    hover_data=["Item Code", "CO2 Total"],
    title="CO₂ by Customer Country and Item Code"
)

# Make sure the bars stack on top of each other rather than side by side
fig.update_layout(
    barmode="stack",
    xaxis_title="CO₂ Total",
    yaxis_title="Customer Country"
)

fig.show()
df = pd.read_csv("../data/detailed_report.csv")
df_agg = df.groupby("Customer City").agg({"Euros": "sum", "CO2 Total": "sum"}).reset_index()
threshold = 500  # Only label if CO2 > 500
df_agg["Label"] = np.where(df_agg["CO2 Total"] > threshold, df_agg["Customer City"], "")

fig = px.scatter(
    df_agg,
    x="Euros",         # Horizontal axis
    y="CO2 Total",        # Vertical axis
    text="Label", # Show the city names on each data point
    #color="Delivery Mode",   # optional grouping
    size="CO2 Total",        # optional sizing by CO₂
    hover_data=["Customer City"],  # optional extra data in hover tooltip
    title="CO₂ = f(Turnover) by City Destination"
)

# Position the text labels nicely (above each point)
fig.update_traces(textposition="top center")

# Optionally format axes (e.g., showing 0.5M or 1.5M for large values):
fig.update_layout(
    xaxis=dict(
        title="Turnover (Euros)",
        tickformat=".1f",   # show one decimal, e.g., 0.5M
    ),
    yaxis=dict(
        title="CO₂ Total (kg or relevant unit)"
    ),
    template="simple_white"
)

fig.show()